import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'home.settings')
django.setup(set_prefix=False)

#
from week1.models import Student, Score
from django.db.models import Q, Avg, Min, Sum, Count, Max

# 添加student.txt学生信息到mysql
stu_mgr = Student.objects

# stu_mgr.create(name="wyh", age=10, gender=2)
# stu_mgr.create(name="zqy", age=12, gender=2)
# stu_mgr.create(name="hmb", age=12, gender=2)
# stu_mgr.create(name="wz", age=13, gender=2)
# stu_mgr.create(name="lmf", age=13, gender=2)
# stu_mgr.create(name="lmf", age=11, gender=2)
# stu_mgr.create(name="ycf", age=11, gender=2)
# stu_mgr.create(name="lf", age=10, gender=2)
# stu_mgr.create(name="wbw", age=12, gender=2)
# stu_mgr.create(name="mxw", age=12, gender=2)

scr_mgr = Score.objects
# scr_mgr.create(class_name="yuwen", class_score=90, student_id=2)
# scr_mgr.create(class_name="math", class_score=80, student_id=2)
# scr_mgr.create(class_name="english", class_score=75, student_id=2)

# scr_mgr.create(class_name="yuwen", class_score=95, student_id=3)
# scr_mgr.create(class_name="math", class_score=90, student_id=3)
# scr_mgr.create(class_name="english", class_score=98, student_id=3)
#
# scr_mgr.create(class_name="yuwen", class_score=80, student_id=4)
# scr_mgr.create(class_name="math", class_score=89, student_id=4)
# scr_mgr.create(class_name="english", class_score=70, student_id=4)
#
#
# scr_mgr.create(class_name="yuwen", class_score=60, student_id=5)
# scr_mgr.create(class_name="math", class_score=75, student_id=5)
# scr_mgr.create(class_name="english", class_score=65, student_id=5)
#
# scr_mgr.create(class_name="yuwen", class_score=81, student_id=6)
# scr_mgr.create(class_name="math", class_score=82, student_id=6)
# scr_mgr.create(class_name="english", class_score=55, student_id=6)
#
# scr_mgr.create(class_name="yuwen", class_score=78, student_id=7)
# scr_mgr.create(class_name="math", class_score=89, student_id=7)
# scr_mgr.create(class_name="english", class_score=65, student_id=7)
#
# scr_mgr.create(class_name="yuwen", class_score=89, student_id=8)
# scr_mgr.create(class_name="math", class_score=60, student_id=8)
# scr_mgr.create(class_name="english", class_score=49, student_id=8)
#
# scr_mgr.create(class_name="yuwen", class_score=89, student_id=9)
# scr_mgr.create(class_name="math", class_score=92, student_id=9)
# scr_mgr.create(class_name="english", class_score=79, student_id=9)
#
# scr_mgr.create(class_name="yuwen", class_score=50, student_id=10)
# scr_mgr.create(class_name="math", class_score=60, student_id=10)
# scr_mgr.create(class_name="english", class_score=62, student_id=10)

# ret = stu_mgr.first().values('score__class_name')
# print(ret, type(ret),)

# 基于对象查询
# 一对多
# 正向
obj = scr_mgr.filter(class_name='yuwen').last()
print(obj.student.name, obj.student.age)
# 反向
# obj = stu_mgr.get(name='zqy')
# ret = obj.score_set.all().values('class_name', 'class_score')
# print(ret)

# 基于双下划线的方式 连表查询
# 一堆多 正向
# 查询分数是65的对应的学生和科目
# ret = scr_mgr.filter(class_score=65).values('student__name', 'class_name')
# print(ret, type(ret))
# 反向查询
# 查询学生是wz 对应所有科目的成绩
ret = stu_mgr.filter(name='wz').values('name', 'score__class_name', 'score__class_score')
# print(ret)
ret = scr_mgr.filter(student__name='wz').values('student__name', 'class_name')
print(ret)


# 作业
"""
3. 依次查询出符合下面条件的信息：
3.1 总成绩大于250分的学生信息
3.2 语文成绩在80-90分之间的学生信息
3.3 有一门科目低于60分的学生信息
3.4 平均分70以上的学生信息
3.5 三门成绩都大于90分的学生姓名 
"""
# 3.1
ret = stu_mgr.annotate(total_score=Sum('score__class_score')).filter(total_score__gt=260).values()
print("3.1", ret)
# 3.2
ret = stu_mgr.filter(Q(score__class_score__gte=80) & Q(score__class_score__lte=90), score__class_name='yuwen').values('name', 'age', 'score__class_name', 'score__class_score')
print("3.2", ret)
# 3.3
ret = scr_mgr.filter(class_score__lt=60).values('student__name', 'class_name', 'class_score')
print("3.3", ret)
# 3.4
ret = stu_mgr.annotate(per_avg_score=Avg('score__class_score')).filter(per_avg_score__gt=70).values('name', 'per_avg_score')
print("3.4", ret)
# 3.5
ret = stu_mgr.filter(Q(score__class_score__gte=90) & Q(score__class_name='yuwen') & Q(score__class_name='math')).values('name')
print("3.5", ret)

